package com.video.internationalvideodisplayproject.repository;

import com.video.internationalvideodisplayproject.pojo.BlVideoShootReqHd;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface BlVideoShootReqHdRepository extends JpaRepository<BlVideoShootReqHd, Long> {

    @Query("SELECT v FROM BlVideoShootReqHd v WHERE v.cancelSign = 'N' order by v.billDate desc")
    List<BlVideoShootReqHd> findAllActive();

    @Query("SELECT v FROM BlVideoShootReqHd v WHERE v.cancelSign = 'N' AND v.shootCategory = :category")
    List<BlVideoShootReqHd> findByShootCategory(@Param("category") String category);

    @Query("SELECT v FROM BlVideoShootReqHd v WHERE v.cancelSign = 'N' AND " +
           "(LOWER(v.videoName) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(v.outDisplayTxt) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(v.keywords) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(v.topicName) LIKE LOWER(CONCAT('%', :keyword, '%')))")
    List<BlVideoShootReqHd> searchByKeyword(@Param("keyword") String keyword);

    @Query("SELECT v FROM BlVideoShootReqHd v WHERE v.cancelSign = 'N' AND " +
           "v.shootCategory = :category AND " +
           "(LOWER(v.videoName) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(v.outDisplayTxt) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(v.keywords) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(v.topicName) LIKE LOWER(CONCAT('%', :keyword, '%')))")
    List<BlVideoShootReqHd> findByCategoryAndKeyword(@Param("category") String category,
                                                     @Param("keyword") String keyword);

    @Query("SELECT v FROM BlVideoShootReqHd v WHERE v.cancelSign = 'N' AND v.topicName = :topicName")
    List<BlVideoShootReqHd> findByTopicName(@Param("topicName") String topicName);

    @Query("SELECT v FROM BlVideoShootReqHd v WHERE v.cancelSign = 'N' AND v.shootCategory = '002'")
    List<BlVideoShootReqHd> selectAllTopicInfo();
}
